********************************************FOLLOW THE LAB SHEET 9***************************************************************************************
---------------------------------------------------------------------------------------------------------------------------------------------------------

<configuration>
.
.
<connectionStrings>
<add name="ConString" connectionString="Data Source= SERVERNAME; Initial Catalog= DBNAME;Integrated Security=True "
providerName="System.Data.SqlClient" />
</connectionStrings>.
.
</configuration>


Configuration information is stored in XML-based text files. You can use any standard text editor or XML parser to create and edit ASP.NET configuration files.

<connectionStrings>
<add>Adds a connection string as a name/value pair to the collection of connection strings.
	name
	Data Source= SERVERNAME
	Initial Catalog= DBNAME
	Integrated Security=True
	providerName

--------------------------------------------------------------------------------------------------------------------------------------------------------


--------------------------------------------------------------------------------------------------------------------------------------------------------
using System.Data;

The System.Data namespace provides access to classes that represent the .NET architecture
--------------------------------------------------------------------------------------------------------------------------------------------------------


--------------------------------------------------------------------------------------------------------------------------------------------------------
using System.Configuration

The System.Configuration namespace contains the types that provide the programming model for handling configuration data.
--------------------------------------------------------------------------------------------------------------------------------------------------------


DB CONNECT CLASS

--------------------------------------------------------------------------------------------------------------------------------------------------------

public static SqlConnection NewCon;//open connection to a SQL Server database. 

string ConStr  //Declare a string to hold the connection string

ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;

	ConfigurationManager //Provides access to configuration files for client applications. 
	ConnectionStrings // Gets the data for the current application's default configuration.
	["ConString"] // connection name that we mention in webconfig

GetConnection() // Tries to establish a connection with the data source

NewCon = new SqlConnection(ConStr)
	ConStr // it has access to configuration files for client applications as a string
	NewCon = new SqlConnection(ConStr) // connect configuration files to sqlconnection(newcon)(create object)

return NewCon // return the sql connection

using System.Data.SqlClient // The System.Data.SqlClient namespace is the.NET Framework Data Provider for SQL Server.

--------------------------------------------------------------------------------------------------------------------------------------------------------



DB ACCESS CLASS

--------------------------------------------------------------------------------------------------------------------------------------------------------

SqlConnection conn // open connection to a SQL Server database. 

conn = DB_Connect.GetConnection(); // establish a connection with the data source

DB_Access dba = new DB_Access(); // obtain data from the database through DB_Access class(create object)

conn.State.ToString() == "Closed" 
	State // Gets the current state of the connection.
	State.ToString() // current state convert to string

conn.Open(); // The Open method opens a connection to a data source.

SqlCommand newCmd = conn.CreateCommand();
	//The SqlCommand class is in the System.SqlClient namespace. It is used to execute operations on a database and retrieve data. 

newCmd.Connection = conn; // Specifies the connection to the database (newcmd object connect with conn0

newCmd.CommandType = CommandType.Text; // Specifies the type of command to be executed (type is text)CommandType is defined in the System.Data namespace. 

newCmd.CommandText = " select * from dbo.zStudent_Details"; // Contains the text of a SQL query 

SqlDataAdapter da = new SqlDataAdapter(newCmd); // Gets or sets a Transact-SQL statement or stored procedure used to select records in the data source.

DataSet ds = new DataSet(); // The DataSet, which is an in-memory cache of data retrieved from a data source

da.Fill(ds, "Students"); 
	Fill // Adds or refreshes rows in the DataSet to match those in the data source.

conn.Close(); // close a connection to a data source.



--------------------------------------------------------------------------------------------------------------------------------------------------------



Display.aspx

--------------------------------------------------------------------------------------------------------------------------------------------------------

DB_Access dba = new DB_Access(); // create new object in DB_Access

DataSet ds = dba.getStudentDetails(); // return the getStudentDetails() function and set it into ds

dgvStudentInfo.DataSource = ds.Tables["Students"].DefaultView;
	.Tables // Gets the collection of tables contained in the DataSet.
	.DefaultView // Gets a customized view of the table which may include a filtered view

dgvStudentInfo.DataBind(); // Data Binding is binding controls to data from database


--------------------------------------------------------------------------------------------------------------------------------------------------------




INSERT

DB_Access class - insertStudent

--------------------------------------------------------------------------------------------------------------------------------------------------------

Firtstly we must check the primary key

SqlDataReader dr = newCmd1.ExecuteReader();
	SqlDataReader // Provides a way of reading a forward-only stream of rows from a SQL Server database
	.ExecuteReader() // Sends the CommandText to the Connection and builds a SqlDataReader.

.HasRows // Gets a value that indicates whether the SqlDataReader contains one or more rows. 


If there is no such a primary key,then we can input values

dr.Close() // close the sqldataReader

.ExecuteNonQuery() // Executes a Transact-SQL statement against the connection and returns the number of rows affected.

return status // return the status true or false by method insertStudent




Insert.aspx - Insert button click event

bool insertStatus = dba.insertStudent(sID, sName, sAddress)
	// pass parameters to method insertStudent (inside in db access class)
	// return true or false and it assign into insertStatus

--------------------------------------------------------------------------------------------------------------------------------------------------------




DELETE  Records

--------------------------------------------------------------------------------------------------------------------------------------------------------

fill the drop down list when the form is loading - method  getAllSids'



DataTable // Represents one table of in-memory data.

DataTable() // Initializes a new instance of the DataTable class with no arguments.

.Columns // Gets the collection of columns that belong to this table.

.Rows // Gets the collection of rows that belong to this table.

.Rows.Add // add new row

return dt; // return datatable dt



DB_Access class - Page_Load event

.DataSource // Gets or sets the data source that the connector binds to.

.DataValueField // Gets or sets the field of the data source that provides the value of each list item.

.DataTextField // Gets or sets the field of the data source that provides the text content of the list items.

.DataBind() // Use this method to bind data from a source to a server control. This method is commonly used after retrieving a dataset through a database 			query.



DB_Access class - method deleteStudent

newCmd.CommandText = "delete from dbo.zStudent_Details where S_ID ='"+sId+ "'";
		// command for delete record


Delete.aspx - Delete button event

dba.deleteStudent(ddlSid.SelectedItem.ToString() 
	// pass parameters to method deleteStudent (inside in db access class)
	


--------------------------------------------------------------------------------------------------------------------------------------------------------




UPDATE Records


--------------------------------------------------------------------------------------------------------------------------------------------------------
create and fill the drop down list like privious method


DB_Access - method  getOneStudentRecord


dt.Rows.Add(dr["S_Name"], dr["S_Address"]) // search sid ,add it's data to a new row in dt


ddlStudentID_SelectedIndexChanged event.

dba.getOneStudentRecord(ddlStudentID.SelectedItem.ToString())
	// pass parameters to method getOneStudentRecord (inside in db access class)
	// return the dt 

txtSname.Text = dt.Rows[0]["SName"].ToString(); // get row 0 value and convert it into string then put into textbox



DB_Access - method updateStudent

like insert or delete method that we mention


Update button press event.

// pass parameters to method updateStudent
--------------------------------------------------------------------------------------------------------------------------------------------------------




